<?php if (!defined('BASEPATH')) exit('No direct script access allowed');

class Classes_model extends CI_Model {

    function __construct() {
        parent::__construct();
    }

    function getClasses($class_id = null){
            $sql = "
            SELECT c.id, count( s.id ) as students_nb
            FROM classes c
            LEFT JOIN students s ON c.id = s.class_id
            ".(is_null($class_id) ? '' : ' WHERE c.id = \''.$class_id.'\' ')."
            GROUP BY c.id
            ";
            $query = $this->db->query($sql)->result();
        if (is_null($class_id)) {
            return $query;
        } else {
            return $query[0];
        }
    }

    function getFreeStudents(){
        $sql = "
        SELECT u.id, firstname, lastname
        FROM users u, user_profiles up
        WHERE role_id = 3
        AND u.id = up.user_id
        AND u.id not in (select id from students);
        ";
        return $this->db->query($sql)->result();
    }

    function getStudentByClass($class_id){
        $sql = "
        SELECT u.id, firstname, lastname
        FROM users u, user_profiles up
        WHERE role_id = 3
        AND u.id = up.user_id
        AND u.id in (select id from students where class_id='".$class_id."');
        ";
        return $this->db->query($sql)->result();
    }

    function newStudent($class_id, $student_id){
        $this->db->set('id',$student_id)
                 ->set('class_id', $class_id);
        if ($this->db->insert('students')){
            return true;
        }
        return false;
    }

    function removeStudent($student_id){
        $this->db->where('id', $student_id);
        if ($this->db->delete('students')){
            return true;
        }
        return false;
    }

    function removeClass($class_id){
        $sql = "delete from students where class_id='".$class_id."'";
        if ($this->db->query($sql)){
            $sql = "delete from classes where id='".$class_id."'";
            if ($this->db->query($sql)){
                return true;
            }
        }
        return false;
    }

    function addClass($class){
        $this->db->set('id',$class);
        if ($this->db->insert('classes')){
            return true;
        }
        return false;
    }

    function get_by_teacher($teacher_id) {
        return $this->db->get('classes')->result();
    }
}